## Table L.1:Donation & Lobbying and Approval of Miscellaneous Tariff Bill: ----

## Instructions ----------------------------------------------------------------
# Step 1: Adjust DATA_DIR to where data is is located
# Step 2: Adjust OUT_DIR to where output folder is
# Step 3: Run entire script
## setup -----------------------------------------------------------------------

# clean slate
rm(list = ls())
# Need to install to read/use PanelEstimate Objects
# devtools::install_github("insongkim/PanelMatch",ref = "big_data_checks")

## library
library(dplyr)
library(data.table)
library(PanelMatch)
library(kableExtra)
options(scipen=999)

DATA_DIR <- "C:/Users/js.egb/Dropbox/campaign-lobby-paper/replication_package/data"
OUT_DIR <- "C:/Users/js.egb/Dropbox/campaign-lobby-paper/replication_package/output"    



#### 7. Descriptive Analysis ####
# want to check whether company donated to Senator in the same state
# combine with lobbying on mtb or not. 
# also check who are the 6 companies that are publicly traded, proponents, but no MTB lobbying.
# also check on the 61 companies that lobby on mtbs but do not appear as proponents
setwd(dirname(rstudioapi::getActiveDocumentContext()$path))
library(dplyr)
library(data.table)


# # Load necessary data
# load(file.path(DATA_DIR, "d_final_quarter_20210521.RData")) # for examples

# list of tariff bills with clients
mtb <- readr::read_csv(file.path(DATA_DIR, "mtb_final.csv"))

# panel examples
mtb_final <- readr::read_csv(file.path(DATA_DIR, "mtb_final_panel.csv"))

# omit Jedwards. erroneously linked to Jed Oil. Jedwards is no publicly traded company.
# Jedwards is only present in the "mtb" file
mtb$gvkey[mtb$gvkey=="157947"] <- NA

# cross-sectional MTB data, with lobbying and donation data
mtb_desc_final <- readr::read_csv(file.path(DATA_DIR, "mtb_desc_final.csv")) %>%
  filter(mtb_lob == 1) %>%
  mutate(don = ifelse(don_tot_bin == 1 | mtb_don_comm == 1, 1, 0))

# analysis
rows_n <- 14
mtb_tab <- data.frame(text1 = rep(NA, rows_n),
                      freq1 = rep(NA, rows_n),
                      prop1 = rep(NA, rows_n))


# 1. Upper Part of Table  
mtb_tab$text1[1] <- "Total number of MTBs, with:"
mtb_tab$text1[2] <- "- publicly traded proponent firm"
mtb_tab$text1[3] <- "- publicly traded proponent firm that lobbies"
mtb_tab$text1[4] <- "- publicly traded proponent firm lobbies on MTBs, 2008-2010"
mtb_tab$text1[5] <- "Total number of Proponent Firms, that are:"
mtb_tab$text1[6] <- "- publicly traded"
mtb_tab$text1[7] <- "- publicly traded and lobby"
mtb_tab$text1[8] <- "- publicly traded and lobby on MTBs, 2008-2010"

# bill-level data
mtb_tab$freq1[1] <- length(unique(mtb$bill))
mtb_tab$freq1[2] <- length(unique(mtb$bill[!is.na(mtb$gvkey)]))
mtb_tab$freq1[3] <- length(unique(mtb$bill[mtb$gvkey %in% unique(mtb_final$gvkey[mtb_desc_final$mtb_proponent==1])])) # 203 MTBs with LobbyView gvkey
mtb_tab$freq1[4] <- length(unique(mtb$bill[mtb$gvkey %in% unique(mtb_desc_final$gvkey[mtb_desc_final$mtb_proponent==1 & mtb_desc_final$mtb_lob == 1])])) # 182 MTBs with LobbyView gvkey
mtb_tab$prop1[2] <- mtb_tab$freq1[2]/length(unique(mtb$bill))
mtb_tab$prop1[3] <- mtb_tab$freq1[3]/length(unique(mtb$bill))
mtb_tab$prop1[4] <- mtb_tab$freq1[4]/length(unique(mtb$bill))
# firm-level data
mtb$name_new <- ifelse(!is.na(mtb$gvkey), mtb$gvkey, mtb$proponent)
mtb_tab$freq1[5] <- length(unique(mtb$name_new)) # 145
mtb_tab$freq1[6] <- length(unique(na.omit(mtb$gvkey))) # 47 gvkeys 
mtb_tab$freq1[7] <- sum(mtb_desc_final$mtb_proponent) # 44
mtb_tab$freq1[8] <- sum(mtb_desc_final$mtb_proponent==1 & mtb_desc_final$mtb_lob==1) # 37
mtb_tab$prop1[6] <- length(unique(na.omit(mtb$gvkey)))/length(unique(mtb$name_new)) 
mtb_tab$prop1[7] <- sum(mtb_desc_final$mtb_proponent)/length(unique(mtb$name_new))
mtb_tab$prop1[8] <- sum(mtb_desc_final$mtb_proponent==1 & mtb_desc_final$mtb_lob==1)/length(unique(mtb$name_new))


# lower part of the table: probabilities
mtb_tab$text1[9] <- "Publicly traded firms that lobbied on MTBs"
mtb_tab$text1[10] <- "Publicly traded firms that only lobbied on MTBs"
mtb_tab$text1[11] <- "- out of which: firms that get MTBs introduced"
mtb_tab$text1[12] <- "Publicly traded firms that lobbied on MTBs and donated"
mtb_tab$text1[13] <- "- out of which: firms that get MTBs introduced"
mtb_tab$text1[14] <- "MTB Introduction Probability, Difference (in %)"

# frequencies
mtb_tab$freq1[9] <- sum(mtb_desc_final$mtb_lob==1 | mtb_desc_final$don==1)
mtb_tab$freq1[10] <- sum(mtb_desc_final$mtb_lob==1 & mtb_desc_final$don==0)
mtb_tab$freq1[11] <- sum(mtb_desc_final$mtb_lob==1 & mtb_desc_final$mtb_introduced==1 & mtb_desc_final$don==0)
mtb_tab$freq1[12] <- sum(mtb_desc_final$mtb_lob==1 & mtb_desc_final$don==1)
mtb_tab$freq1[13] <- sum(mtb_desc_final$mtb_introduced==1 & mtb_desc_final$don==1)

mtb_tab$prop1[11] <- mean(mtb_desc_final$mtb_introduced[mtb_desc_final$mtb_lob==1 & mtb_desc_final$don==0])
mtb_tab$prop1[13] <- mean(mtb_desc_final$mtb_introduced[mtb_desc_final$mtb_lob==1 & mtb_desc_final$don==1])
mtb_tab$prop1 <- round(mtb_tab$prop1, 3)
mtb_tab$prop1[14] <- mtb_tab$prop1[13] - mtb_tab$prop1[11]
mtb_tab$prop1[14] <- paste0(mtb_tab$prop1[14], " (+", (round(mean(mtb_desc_final$mtb_introduced[mtb_desc_final$mtb_lob==1 & mtb_desc_final$don==1])/mean(mtb_desc_final$mtb_introduced[mtb_desc_final$mtb_lob==1 & mtb_desc_final$don==0]), 3)-1)*100, "%)")
# frequencies easier to understand
mtb_tab$freq1 <- as.character(mtb_tab$freq1)

colnames(mtb_tab) <- c("", "Frequency", "Proportion")
library(kableExtra)
mtb_tab %>%
  replace(is.na(.), "") %>%
  kable(booktabs = TRUE, format = "latex", align = c("lcc")) %>%
  kable_styling(latex_options = c("hold_position")) %>%
  pack_rows("A. Bills", 1, 4) %>%
  row_spec(4, bold = FALSE, hline_after = TRUE) %>%
  pack_rows("B. Proponents", 5, 8, latex_gap_space = "1em") %>%
  row_spec(8, bold = FALSE, hline_after = TRUE) %>%
  pack_rows("C. Lobbying & Donations", 9, 12, latex_gap_space = "1em") %>%
  row_spec(0, bold = TRUE) %>%
  row_spec(13, bold = FALSE, hline_after = TRUE) %>%
  row_spec(14, bold = FALSE, background = "gray!6") %>%
  save_kable(file.path(OUT_DIR, "table_l1.tex")) # mtb_tab.tex 


#### Text Example. Best Buy (gvkey 002184), Amy Klobuchar (D, Minnesota, govtrack ID: 412242). ####
# Bill introduced in Senate in October 22, 2009.
# bill info
as.data.frame(mtb[mtb$bill=="s2086",])
# donations
# d_final_quarter %>%
#   filter(chamber=="senate", 
#          gvkey == "002184", 
#          govtrack_id == "412242", 
#          report_year %in% c(2009:2011)) %>%
#   select(gvkey, conml, year_quarter, 
#          govtrack_id, first_name, last_name, state, party,
#          don_pac:don_tot)
# # lobbying
# mtb_final[mtb_final$gvkey=="002184",]

### Companies (6) that are proponents but do not lobby. ####
mtb %>%
  mutate(no_lobby_mtb = ifelse(!gvkey %in% mtb_desc_final$gvkey, 1, 0)) %>%
  filter(!is.na(gvkey), no_lobby_mtb == 1) %>%
  select(proponent, gvkey, date_introduced, date_approved) %>%
  distinct()

# [1] Jarden Consumer Solutions: never lobbies on MTBs
# [2] "Jedwards International": not publicly traded. Remove.
# [3] "Jo-Ann Stores": No lobbying before 2011.                     
# [4] "Spirit Aerosystems": in lobbyview, but not linked to gvkey. Lobbying on MTBs but no gvkey link.
# [5] "Chevron Phillips Chemical Company": multiple chevron clients, no chevron phillips
# [6] General Electric Corp.: lobbies, but never lobbies on MTBs, 2009-2011

rm(list = ls())
